//    Copyright (C) Kherty.  All rights reserved.
using System;
using System.Collections.Generic;
using System.Windows;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using GenericGridControl.Model;
using OpenLS.Drawing;
using OpenLS.Drawing.UI;
using OpenLS.Spreadsheet.Expressions;
using OpenLS.Spreadsheet.Internals;
using OpenLS.Spreadsheet.UI.Internals;
using OpenLS.Spreadsheet.UI.Internals.Resources;
using OpenLS.UI;
using QuantumWorkspaces.DomainEntities;

namespace OpenLS.Spreadsheet.UI
{
    internal class GridFormatting
    {
        internal static void True(object sender, CanExecuteRoutedEventArgs args)
        {
            args.CanExecute = true;
            args.Handled = true;
        }

        private static Worksheet getWorksheet(object sender)
        {
            return WorksheetHelper.GetWorksheet(sender);
        }

        private static void onRepeatLastCommand(object sender, ExecutedRoutedEventArgs e)
        {
            Worksheet ws = getWorksheet(sender);
            if (ws.Workbook.UndoManager.RedoStack.Count > 0)
                return;
            DependencyObject root = EditionElement.GetEditorRoot((DependencyObject) sender);
            ExecutedCommand executedCommand = EditionElement.GetLastExecutedCommand(root);
            if (executedCommand != null)
            {
                executedCommand.Command.Execute(executedCommand.Parameter, (IInputElement) sender);
            }
            e.Handled = true;
        }


        internal static void EnableCommands(UIElement g)
        {
            g.CommandBindings.Add(new CommandBinding(OfficeApplicationCommands.RepeatLastCommand,
                                                     onRepeatLastCommand, True));
            g.InputBindings.Add(new KeyBinding(OfficeApplicationCommands.RepeatLastCommand, Key.F4, ModifierKeys.None));
            g.InputBindings.Add(new KeyBinding(ApplicationCommands.Redo, Key.Y, ModifierKeys.Control));

            g.CommandBindings.Add(new CommandBinding(ThemeCommands.SetTheme, onSetTheme, True));
            g.CommandBindings.Add(new CommandBinding(ThemeCommands.SetColorScheme, onSetColorScheme, True));
            g.CommandBindings.Add(new CommandBinding(ThemeCommands.SetFontScheme, onSetFontScheme, True));
            g.CommandBindings.Add(new CommandBinding(SpreadsheetCommands.SetTabColor, OnSetTabColor, True));
            GridCommandHelper.AddRepeatableWorksheetCommandBinding(g, SpreadsheetCommands.SetStyle, onSetStyle,
                                                                   WorksheetHelper.FormatCellProtectionFunction);

            GridCommandHelper.AddRepeatableWorksheetCommandBinding(g, SpreadsheetCommands.FormatAsTable, onFormatAsBigDataTable,
                                                                   onIsLoggedWithWorksheet);

            GridCommandHelper.AddRepeatableWorksheetCommandBinding(g, SpreadsheetCommands.MarketData, onMarketData,
                                                                   onIsLoggedWithWorksheet);

            GridCommandHelper.AddRepeatableWorksheetCommandBinding(g, SpreadsheetCommands.FormatAsStandardTable, onFormatAsStandardTable,
                                                                   WorksheetHelper.FormatCellProtectionFunction);
            GridCommandHelper.AddRepeatableWorksheetCommandBinding(g, SpreadsheetEditingCommands.ClearContents,
                                                                   onClearContents, WorksheetHelper.DefaultProtectionFunction);
            GridCommandHelper.AddRepeatableWorksheetCommandBinding(g, SpreadsheetEditingCommands.ClearComments,
                                                                   onClearComments, WorksheetHelper.DefaultProtectionFunction);
            GridCommandHelper.AddRepeatableWorksheetCommandBinding(g, SpreadsheetEditingCommands.ClearHyperlinks,
                                                                   onClearHyperlinks, WorksheetHelper.DefaultProtectionFunction);
            GridCommandHelper.AddRepeatableWorksheetCommandBinding(g, SpreadsheetEditingCommands.ClearFormats,
                                                                   onClearFormats, WorksheetHelper.DefaultProtectionFunction);
            GridCommandHelper.AddRepeatableWorksheetCommandBinding(g, SpreadsheetEditingCommands.ClearAll,
                                                                   onClearAll, WorksheetHelper.DefaultProtectionFunction);
            GridCommandHelper.AddRepeatableWorksheetCommandBinding(g, OfficeApplicationCommands.SetForeground,
                                                                   onSetForeground, WorksheetHelper.FormatCellProtectionFunction);
            GridCommandHelper.AddRepeatableWorksheetCommandBinding(g, OfficeApplicationCommands.SetBackground,
                                                                   onSetBackground, WorksheetHelper.FormatCellProtectionFunction);
            GridCommandHelper.AddRepeatableWorksheetCommandBinding(g, EditingCommands.IncreaseFontSize,
                                                                   onIncreaseFontSize, WorksheetHelper.FormatCellProtectionFunction);
            GridCommandHelper.AddRepeatableWorksheetCommandBinding(g, EditingCommands.DecreaseFontSize,
                                                                   onDecreaseFontSize, WorksheetHelper.FormatCellProtectionFunction);
            GridCommandHelper.AddRepeatableWorksheetCommandBinding(g, EditingCommands.ToggleBold, onToggleBold,
                                                                   WorksheetHelper.FormatCellProtectionFunction);

            GridCommandHelper.AddRepeatableWorksheetCommandBinding(g, SpreadsheetCommands.ToggleCellLock, onToggleCellLock,
                                                                   WorksheetHelper.FormatCellProtectionFunction);
            g.InputBindings.Add(new InputBinding(EditingCommands.ToggleBold, new KeyGesture(Key.B, ModifierKeys.Control)));
            GridCommandHelper.AddRepeatableWorksheetCommandBinding(g, EditingCommands.ToggleUnderline,
                                                                   onToggleUnderline, WorksheetHelper.FormatCellProtectionFunction);
            g.InputBindings.Add(new InputBinding(EditingCommands.ToggleUnderline,
                                                 new KeyGesture(Key.U, ModifierKeys.Control)));
            GridCommandHelper.AddRepeatableWorksheetCommandBinding(g, EditingCommands.ToggleItalic,
                                                                   onToggleItalic, WorksheetHelper.FormatCellProtectionFunction);
            g.InputBindings.Add(new InputBinding(EditingCommands.ToggleItalic,
                                                 new KeyGesture(Key.I, ModifierKeys.Control)));
            GridCommandHelper.AddRepeatableWorksheetCommandBinding(g, EditingCommands.AlignLeft,
                                                                   delegate(object sender, Worksheet worksheet,
                                                                            object parameter)
                                                                       {
                                                                           onSetCellProperty(sender,
                                                                               worksheet, 
                                                                                             CellProperty.
                                                                                                 HorizontalAlignment,
                                                                                             HorizontalCellAlignment.Left);
                                                                           worksheet.Selection.ClearCellValue(
                                                                               CellProperty.Indentation);
                                                                       }, WorksheetHelper.FormatCellProtectionFunction);
            GridCommandHelper.AddRepeatableWorksheetCommandBinding(g, EditingCommands.AlignCenter,
                                                                   delegate(object sender, Worksheet worksheet,
                                                                            object parameter)
                                                                       {
                                                                           onSetCellProperty(sender,worksheet,
                                                                                             CellProperty.
                                                                                                 HorizontalAlignment,
                                                                                             HorizontalCellAlignment.
                                                                                                 Center);
                                                                           worksheet.Selection.ClearCellValue(
                                                                               CellProperty.Indentation);
                                                                       },
                                                                   WorksheetHelper.FormatCellProtectionFunction);
            GridCommandHelper.AddRepeatableWorksheetCommandBinding(g, EditingCommands.AlignRight,
                                                                   delegate(object sender, Worksheet worksheet,
                                                                            object parameter)
                                                                       {
                                                                           onSetCellProperty(sender, worksheet,
                                                                                             CellProperty.
                                                                                                 HorizontalAlignment,
                                                                                             HorizontalCellAlignment.
                                                                                                 Right);
                                                                           worksheet.Selection.ClearCellValue(
                                                                               CellProperty.Indentation);
                                                                       }, WorksheetHelper.FormatCellProtectionFunction);
            GridCommandHelper.AddWorksheetCommandBinding(g, SpreadsheetEditingCommands.VerticalAlignTop,
                                                     (sender, s, a) =>
                                                     onSetCellProperty(sender, s, CellProperty.VerticalAlignment,
                                                                       VerticalCellAlignment.Top),
                                                     WorksheetHelper.FormatCellProtectionFunction);
            GridCommandHelper.AddWorksheetCommandBinding(g, SpreadsheetEditingCommands.VerticalAlignCenter,
                                                     (sender, s, a) =>
                                                     onSetCellProperty(sender, s, CellProperty.VerticalAlignment,
                                                                       VerticalCellAlignment.Center),
                                                     WorksheetHelper.FormatCellProtectionFunction);
            GridCommandHelper.AddWorksheetCommandBinding(g, SpreadsheetEditingCommands.VerticalAlignBottom,
                                                     (sender, s, a) =>
                                                     onSetCellProperty(sender, s, CellProperty.VerticalAlignment,
                                                                       VerticalCellAlignment.Bottom),
                                                     WorksheetHelper.FormatCellProtectionFunction);

            GridCommandHelper.AddRepeatableWorksheetCommandBinding(g, EditingCommands.IncreaseIndentation,
                                                                   onIncreaseIndentation, WorksheetHelper.FormatCellProtectionFunction);
            GridCommandHelper.AddRepeatableWorksheetCommandBinding(g, EditingCommands.DecreaseIndentation,
                                                                   onDecreaseIndentation, WorksheetHelper.FormatCellProtectionFunction);

            GridCommandHelper.AddRepeatableWorksheetCommandBinding(g, SpreadsheetEditingCommands.ToggleTextWrapping,
                                                                   onToggleTextWrapping, WorksheetHelper.FormatCellProtectionFunction);
            GridCommandHelper.AddRepeatableWorksheetCommandBinding(g, SpreadsheetEditingCommands.SetFontFamily,
                                                                   onSetFontFamily, WorksheetHelper.FormatCellProtectionFunction);
            GridCommandHelper.AddRepeatableWorksheetCommandBinding(g, SpreadsheetEditingCommands.SetFontSize,
                                                                   onSetFontSize, WorksheetHelper.FormatCellProtectionFunction);
            GridCommandHelper.AddWorksheetCommandBinding(g, SpreadsheetCommands.SetRowHeight,
                                                     onSetRowHeight, WorksheetHelper.ProtectionFunction(p => p.FormatRows));
            GridCommandHelper.AddWorksheetCommandBinding(g, SpreadsheetCommands.AutoFitRowHeight,
                                                     onAutoFitRowHeight, WorksheetHelper.ProtectionFunction(p => p.FormatRows));
            GridCommandHelper.AddWorksheetCommandBinding(g, SpreadsheetCommands.AutoFitColumnWidth,
                                                     onAutoFitColumnWidth, WorksheetHelper.ProtectionFunction(p => p.FormatColumns));
            GridCommandHelper.AddWorksheetCommandBinding(g, SpreadsheetCommands.SetColumnWidth,
                                                     onSetColumnWidth, WorksheetHelper.ProtectionFunction(p => p.FormatColumns));
        }

        private static void OnSetTabColor(object sender, ExecutedRoutedEventArgs e)
        {
            var worksheet = getWorksheet(sender);
            worksheet.EnsureProperties().TabColor = (SpreadsheetColor) e.Parameter;
        }

        static void onIsLoggedWithWorksheet(object sender, CanExecuteRoutedEventArgs args)
        {
            args.CanExecute = (QuantumUserSession.IsLogged && (getWorksheet(sender) != null));
            args.Handled = true;
        }

        static void onMarketData(Worksheet worksheet, object parameter)
        {
            var s = (TableStyle)parameter;
            Table table = null;

            // First step: check intersection with other BigDatatables
            foreach (Table t in Enumerable.Distinct(Enumerable.Where(
                                                        Enumerable.Select(worksheet.Selection.GetNonEmptyCells(),
                                                                          cell => cell.Table), tt => tt != null)))
            {

                if (table != null)
                    return;
                table = t;
            }
            if (table != null)
            {
                if (table.IsLocal == false)
                {
                    table.Style = s;
                    return;
                }
                else
                {
                    table.Style = s;
                }
            }
            // End of First step: check intersection with other BigDatatables

            using (worksheet.Workbook.CreateUndoScope(StringTable.Table))
            {
                TableDefinedNameControl w = new TableDefinedNameControl();
                TableDefinedName newName = new TableDefinedName(worksheet.Workbook);
                newName.Expression = new RangeReferenceExpression(worksheet.Selection.ToReference(), worksheet);
                w.DataContext = newName;

                WindowServices.ShowDialogOkCancel(w, UIStringTable.NewName, null,
                     delegate
                     {

                         // newName.Comment
                         dynamic selectedItem = w.TableAuthorizations.SelectedItem;
                         bool isReadOnly = false;
                         bool isPublic = false;
                         bool allowsUpdate = false;
                         bool allowsInsert = false;
                         bool allowsDelete = false;

                         if (selectedItem.Content == UIStringTable.IsReadonlyTable)
                         {
                             isReadOnly = true;
                             isPublic = true;
                             allowsUpdate = false;
                             allowsInsert = false;
                             allowsDelete = false;
                         }
                         if (selectedItem.Content == UIStringTable.IsInsertOnlyTable)
                         {
                             isReadOnly = false;
                             isPublic = true;
                             allowsUpdate = false;
                             allowsInsert = true;
                             allowsDelete = false;
                         }
                         if (selectedItem.Content == UIStringTable.IsInsertUpdateDeleteTable)
                         {
                             isReadOnly = false;
                             isPublic = true;
                             allowsUpdate = true;
                             allowsInsert = true;
                             allowsDelete = true;
                         }
                         if (selectedItem.Content == UIStringTable.IsPrivateTable)
                         {
                             isReadOnly = false;
                             isPublic = false;
                             allowsUpdate = false;
                             allowsInsert = false;
                             allowsDelete = false;
                         }
                         if (!BigDataTableStaticDAL.CheckNameAvailability(w.NameTextBox.Text, isPublic))
                         {
                             MessageBox.Show("Name already exists on the server");

                             return;
                         }
                         table = worksheet.AddTable(worksheet.Selection, s, w.NameTextBox.Text, newName.Comment, isPublic, isReadOnly, allowsUpdate, allowsDelete, allowsInsert);
                         foreach (Column column in table.Range.GetColumns())
                         {
                             column.AutoFit();
                         }
                         // Notify the main GUI that a new table has been added.
                         worksheet.Workbook.NotifyBigDataTableAdded(table, "MARKETDATA");
                         worksheet.Workbook.TableDefinedNames.Add(newName);
                     },
                    // Cancel button delegate
                    delegate
                    {
                    }
                );
            }


        }


        private static void onAutoFitColumnWidth(Worksheet worksheet, object oo)
        {
            Range r = worksheet.Selection;
            if (r == null)
                return;
            var fullColumnRange = r as FullColumnRange;
            if (fullColumnRange == null && r is CellsRange)
            {
                fullColumnRange = ((CellsRange) r).ToFullColumnRange();
            }
            if (fullColumnRange == null)
                return; //\\
            foreach (Column column in fullColumnRange.GetDimensions())
                column.AutoFit();
        }

        private static void onAutoFitRowHeight(Worksheet worksheet, object oo)
        {
            Range r = worksheet.Selection;
            if (r == null)
                return;
            var fullRowRange = r as FullRowRange;
            if (fullRowRange == null && r is CellsRange)
            {
                fullRowRange = ((CellsRange) r).ToFullRowRange();
            }
            if (fullRowRange == null)
                return; //\\
            foreach (Row row in fullRowRange.GetDimensions())
                row.AutoFit();
        }

        private static void onSetRowHeight(object sender, Worksheet worksheet, object parameter)
        {
            Range r = worksheet.Selection;
            if (r == null)
                return;
            var fullRowRange = r as FullRowRange;
            if (fullRowRange == null && r is CellsRange)
            {
                fullRowRange = ((CellsRange) r).ToFullRowRange();
            }
            if (fullRowRange == null)
                return; //\\
            var setDimensionControl = new SetDimensionControl
                                          {
                                              TextBox =
                                                  {Text = fullRowRange.FirstRow.ActualHeight.ToString()}
                                          };
            // d.Owner = WindowHelper.GetAncestor<Window>(sender);
            // d.Title = SpreadsheetCommands.SetRowHeight.Text;
            WindowServices.ShowDialog(
                setDimensionControl, SpreadsheetCommands.SetRowHeight.Text, sender as UIElement,
                delegate
                    {
                        {
                            double val = double.Parse(setDimensionControl.TextBox.Text);
                            foreach (Row row in fullRowRange.GetDimensions())
                            {
                                row.Height = val;
                            }
                        }
                    }, o => false);
        }

        private static void onSetColumnWidth(object sender, Worksheet worksheet, object parameter)
        {
            Range r = worksheet.Selection;
            if (r == null)
                return;
            var fullColumnRange = r as FullColumnRange;
            if (fullColumnRange == null && r is CellsRange)
            {
                fullColumnRange = ((CellsRange) r).ToFullColumnRange();
            }
            if (fullColumnRange == null)
                return; //\\
            var setDimensionControl = new SetDimensionControl
                                          {
                                              TextBox = {Text = fullColumnRange.FirstColumn.ActualWidth.ToString()}
                                          };
            //  d.Owner = WindowHelper.GetAncestor<Window>(sender);
            //  d.Title = SpreadsheetCommands.SetColumnWidth.Text;
            WindowServices.ShowDialog(
                setDimensionControl, SpreadsheetCommands.SetColumnWidth.Text, sender as UIElement,
                delegate
                    {
                        double val = double.Parse(setDimensionControl.TextBox.Text);
                        foreach (Column column in fullColumnRange.GetDimensions())
                        {
                            column.Width = val;
                        }
                    }, o=>false);
        }

        private static void onSetCellProperty(object sender, Worksheet worksheet, CellProperty p, object value)
        {
           //\\ Worksheet worksheet = getWorksheet(sender);
            worksheet.Selection.SetCellValue(p, value);
            SpreadsheetElement.ForgetHightlightForAndClipboardMonitor(sender);
        }

        private static void onToggleTextWrapping(Worksheet worksheet, object parameter)
        {
            var range = worksheet.Selection;
            range.ToggleCellValue(CellProperty.TextWrapping);
            foreach (Row row in Enumerable.Where(range.GetRows(), r => !r.CustomHeight))
            {
               row.AutoFit();
            }
        }
        private static void onSetFontFamily(Worksheet worksheet, object parameter)
        {
            worksheet.Selection.SetCellValue(CellProperty.FontFamily, (FontFamily)parameter);
        }
        private static void onSetFontSize(Worksheet worksheet, object parameter)
        {
            worksheet.Selection.SetCellValue(CellProperty.FontSize, Convert.ToDouble(parameter));
        }


        private static void onToggleBold(Worksheet worksheet, object parameter)
        {
            worksheet.Selection.ToggleCellValue(CellProperty.FontWeight);
        }

        private static void onToggleCellLock(Worksheet worksheet, object parameter)
        {
            worksheet.Selection.ToggleCellValue(CellProperty.IsLocked);
        }
        private static void onToggleUnderline(Worksheet worksheet, object parameter)
        {
            if (parameter is UnderlineType)
            {
                var underlineType = (UnderlineType) parameter;
                UnderlineType o = worksheet.Selection.FirstCell.FontUnderline;
                o = o == underlineType ? UnderlineType.None : underlineType;
                worksheet.Selection.SetCellValue(CellProperty.FontUnderline, o);
                return;
            }
            worksheet.Selection.ToggleCellValue(CellProperty.FontUnderline);
        }

        private static void onToggleItalic(Worksheet worksheet, object parameter)
        {
            worksheet.Selection.ToggleCellValue(CellProperty.FontStyle);
        }

        private static void onIncreaseIndentation(Worksheet worksheet, object parameter)
        {
            worksheet.Selection.IncreaseIndentation();
        }

        private static void onDecreaseIndentation(Worksheet worksheet, object parameter)
        {
            worksheet.Selection.DecreaseIndentation();
        }

        private static void onIncreaseFontSize(Worksheet worksheet, object parameter)
        {
            worksheet.Selection.FontSize = ((double) worksheet.Selection.GetFirstCellValue(CellProperty.FontSize)) + 1;
        }

        private static void onDecreaseFontSize(Worksheet worksheet, object parameter)
        {
            worksheet.Selection.FontSize =
                Math.Max(((double) worksheet.Selection.GetFirstCellValue(CellProperty.FontSize)) - 1, 5);
        }

        private static void onSetStyle(Worksheet worksheet, object parameter)
        {
            var s = (CellStyle) parameter;
            worksheet.Selection.SetStyle(s);
        }

        private static void onFormatAsBigDataTable(Worksheet worksheet, object parameter)
        {
            var s = (TableStyle) parameter;
            Table table = null;

            // First step: check intersection with other BigDatatables
            foreach (Table t in Enumerable.Distinct(Enumerable.Where(
                                                        Enumerable.Select(worksheet.Selection.GetNonEmptyCells(),
                                                                          cell => cell.Table), tt => tt != null)))
            {
                
               if (table != null)
                    return;
                table = t;
            }
            if (table != null)
            {
                if (table.IsLocal == false)
                {
                    table.Style = s;
                    return;
                }
                else
                {
                    table.Style = s;
                }
            }
            // End of First step: check intersection with other BigDatatables

            using (worksheet.Workbook.CreateUndoScope(StringTable.Table))
            {
                TableDefinedNameControl w = new TableDefinedNameControl();
                TableDefinedName newName = new TableDefinedName(worksheet.Workbook);
                newName.Expression = new RangeReferenceExpression(worksheet.Selection.ToReference(), worksheet);
                w.DataContext = newName;

                WindowServices.ShowDialogOkCancel(w, UIStringTable.NewName, null, 
                     delegate
                    {
                        
                        // newName.Comment
                        dynamic selectedItem = w.TableAuthorizations.SelectedItem;
                        bool isReadOnly = false;
                        bool isPublic = false;
                        bool allowsUpdate = false;
                        bool allowsInsert = false;
                        bool allowsDelete = false;

                        if (selectedItem.Content == UIStringTable.IsReadonlyTable)
                        {
                            isReadOnly = true;
                            isPublic = true;
                            allowsUpdate = false;
                            allowsInsert = false;
                            allowsDelete = false;
                        }
                        if (selectedItem.Content == UIStringTable.IsInsertOnlyTable)
                        {
                            isReadOnly = false;
                            isPublic = true;
                            allowsUpdate = false;
                            allowsInsert = true;
                            allowsDelete = false;
                        }
                        if (selectedItem.Content == UIStringTable.IsInsertUpdateDeleteTable)
                        {
                            isReadOnly = false;
                            isPublic = true;
                            allowsUpdate = true;
                            allowsInsert = true;
                            allowsDelete = true;
                        }
                        if (selectedItem.Content == UIStringTable.IsPrivateTable)
                        {
                            isReadOnly = false;
                            isPublic = false;
                            allowsUpdate = false;
                            allowsInsert = false;
                            allowsDelete = false;
                        }
                        if (!BigDataTableStaticDAL.CheckNameAvailability(w.NameTextBox.Text, isPublic))
                        {
                            MessageBox.Show("Name already exists on the server");
                            
                            return;
                        }
                        table = worksheet.AddTable(worksheet.Selection, s, w.NameTextBox.Text, newName.Comment, isPublic, isReadOnly, allowsUpdate, allowsDelete, allowsInsert);
                        foreach (Column column in table.Range.GetColumns())
                        {
                            column.AutoFit();
                        }
                        // Notify the main GUI that a new table has been added.
                        worksheet.Workbook.NotifyBigDataTableAdded(table, "BIGDATATABLE");
                        worksheet.Workbook.TableDefinedNames.Add(newName);
                    },
                    // Cancel button delegate
                    delegate
                        {
                        }
                        );               
            }
        }

        private static void onFormatAsStandardTable(Worksheet worksheet, object parameter)
        {
            var s = (TableStyle)parameter;
            Table table = null;

            // First check: the Standard table should not have any intersection with any table (big or not)
            foreach (Table t in Enumerable.Distinct(Enumerable.Where(
                                                        Enumerable.Select(worksheet.Selection.GetNonEmptyCells(),
                                                                          cell => cell.Table), tt => tt != null)))
            {
                if (table != null)
                    return;
                table = t;
            }
            if (table != null)
            {
                table.Style = s;
                return;
            }
            // End of First check

            using (worksheet.Workbook.CreateUndoScope(StringTable.Table))
            {
                NewDefinedNameControl w = new NewDefinedNameControl();
                DefinedName newName = new DefinedName(worksheet.Workbook);
                newName.Expression = new RangeReferenceExpression(worksheet.Selection.ToReference(), worksheet);
                w.DataContext = newName;

                WindowServices.ShowDialog(w, UIStringTable.NewName, null,
                        // Ok Button delegate
                    delegate
                    {

                        
                        table = worksheet.AddTable(worksheet.Selection, s, w.NameTextBox.Text, newName.Comment);
                        foreach (Column column in table.Range.GetColumns())
                        {
                            column.AutoFit();
                        }

                        worksheet.Workbook.DefinedNames.Add(newName);
                    },  o => false
                        );
            }
        }

        private static void onSetTheme(object sender, ExecutedRoutedEventArgs args)
        {
            Worksheet worksheet = getWorksheet(sender);
            worksheet.Workbook.Theme = (Theme) args.Parameter;
            args.Handled = true;
        }

        private static void onSetColorScheme(object sender, ExecutedRoutedEventArgs args)
        {
            Worksheet worksheet = getWorksheet(sender);
            worksheet.Workbook.Theme.ColorScheme = (ColorScheme) args.Parameter;
            args.Handled = true;
        }

        private static void onSetFontScheme(object sender, ExecutedRoutedEventArgs args)
        {
            Worksheet worksheet = getWorksheet(sender);
            worksheet.Workbook.Theme.FontScheme = (FontScheme) args.Parameter;
            args.Handled = true;
        }

        private static void onClearContents(Worksheet worksheet, object parameter)
        {
            worksheet.Selection.ClearContents();
//            worksheet.Selection.Clear(SpreadsheetEditingCommands.ClearContents.Text,
  //                                    RangeFunctions.ApplyValueAndFormula.ClearCell);
            clearTables(worksheet);
        }
        private static void onClearHyperlinks(Worksheet worksheet, object parameter)
        {
            worksheet.Selection.ClearHyperlinks();
        }
        private static void onClearComments(Worksheet worksheet, object parameter)
        {
            worksheet.Selection.ClearContents();
        }

        private static void clearTables(Worksheet worksheet)
        {
            List<Table> tables =
                new List<Table>(worksheet.Tables).FindAll(table => worksheet.Selection.Contains(table.Range));
            foreach (Table table in tables)
            {
                worksheet.RemoveTable(table);
            }
        }

        private static void onClearFormats(Worksheet worksheet, object parameter)
        {
            worksheet.Selection.ClearFormats();
//            worksheet.Selection.Clear(SpreadsheetEditingCommands.ClearFormats.Text,
  //                                    RangeFunctions.ApplyFormats.ClearCell);
        }

        private static void onClearAll(Worksheet worksheet, object parameter)
        {
            worksheet.Selection.Clear();
       //     worksheet.Selection.Clear(SpreadsheetEditingCommands.ClearAll.Text, RangeFunctions.ApplyAll.ClearCell);
            clearTables(worksheet);
        }

        private static void onSetForeground(object sender, Worksheet worksheet, object parameter)
        {
            if (parameter is Color)
                parameter = new RgbColor((Color) parameter);
            worksheet.Selection.SetCellValue(CellProperty.Foreground, parameter);
            SpreadsheetElement.ForgetHightlightForAndClipboardMonitor(sender);
        }

        private static void onSetBackground(object sender, Worksheet worksheet, object parameter)
        {
            if (parameter is Color)
                parameter = new RgbColor((Color) parameter);
            var color = parameter as SpreadsheetColor;
            if (color != null)
            {
                var f = new PatternFill (color, null, PatternType.Solid);
                parameter = f;
            }

            worksheet.Selection.SetCellValue(CellProperty.Background, parameter);
            SpreadsheetElement.ForgetHightlightForAndClipboardMonitor(sender);
        }
    }
}